
DEFINE TEMP-TABLE exc 
    FIELD exc_curr LIKE exr_curr1
    FIELD exc_rate LIKE exr_rate  
    INDEX exc_index IS PRIMARY exc_curr ASCENDING .


RUN comp_avg_cost (INPUT "RMB", INPUT "", INPUT 04/01/04,INPUT 04/30/04,INPUT "351-1090270").



/*create the exchange rate of currency */
PROCEDURE comp_avg_cost.
    
    DEFINE INPUT PARAMETER base_curr  AS CHARACTER.
    DEFINE INPUT PARAMETER site  AS CHARACTER.
    DEFINE INPUT PARAMETER s_date AS DATE.
    DEFINE INPUT PARAMETER e_date AS DATE.
    DEFINE INPUT PARAMETER part   AS CHARACTER.
    
    DEFINE VARIABLE um_rate AS DECIMAL.
    DEFINE VARIABLE qty_tot AS DECIMAL.
    DEFINE VARIABLE qty_chg AS DECIMAL.
    DEFINE VARIABLE cst_tot AS DECIMAL.
    DEFINE VARIABLE cst_avg AS DECIMAL.
    DEFINE VARIABLE qty_max AS DECIMAL.
    DEFINE VARIABLE vend_max AS CHARACTER.
    DEFINE VARIABLE vend_num AS DECIMAL.
    DEFINE VARIABLE tax_amt_in AS DECIMAL.
    DEFINE VARIABLE tax_amt_out AS DECIMAL.

    DEFINE BUFFER exc1 FOR exc.

    CREATE exc.
    ASSIGN exc_curr = base_curr
           exc_rate = 1.0.
    FOR EACH cu_mstr WHERE cu_curr <> base_curr NO-LOCK:
        FOR EACH exr_rate WHERE (exr_start_date <= e_date AND e_date <= exr_end_date)  OR e_date = ? NO-LOCK:

            IF exr_curr1 = base_curr AND exr_curr2 = cu_curr THEN DO:
               FIND FIRST exc WHERE exc_curr = cu_curr NO-ERROR.
               IF NOT AVAILABLE exc THEN DO:
                  CREATE exc.
                  ASSIGN exc_curr = exr_curr2
                         exc_rate = exr_rate.
               END.
            END.

            IF exr_curr2 = base_curr AND exr_curr1 = cu_curr THEN DO:
               FIND FIRST exc WHERE exc_curr = cu_curr NO-ERROR.
               IF NOT AVAILABLE exc THEN DO:
                  CREATE exc.
                  ASSIGN exc_curr = exr_curr1
                         exc_rate = exr_rate2.
               END.
            END.

        END.

    END.

/* OUTPUT TO c:\mytext.txt. */

    FOR EACH pt_mstr NO-LOCK WHERE pt_part BEGINS part : 
        
        /*compute the average cost*/
        qty_max = 0.
        vend_max = "".
        vend_num = 0.
        tax_amt_in = 0.
        tax_amt_out = 0.
        qty_tot = 0.
        cst_tot = 0.
        FOR EACH tr_hist WHERE tr_type = 'rct-po' AND tr_part = pt_part
                           AND (s_date <= tr_date AND tr_date <= e_date) 
                           AND tr_site BEGINS site 
                           AND tr_addr <> "S15032" NO-LOCK, /*Filter the vend S13052 */
            EACH exc WHERE exc_curr = tr_curr
                         BREAK BY tr_part BY tr_addr:

            RUN conv_um(INPUT tr_part,INPUT pt_um, INPUT tr_um, OUTPUT um_rate).

            IF FIRST-OF (tr_addr) THEN 
               qty_chg = 0.
            
            /*accept the receiver in 500 RW03 warehouse N-N-N  and  m/s type could be considered.*/
            IF CAN-DO ("M,S",tr_ship_type) OR tr_status = "N-N-N" THEN DO: 
               qty_chg = qty_chg + tr_qty_loc * um_rate.
            END.
            ELSE DO:
               qty_chg = qty_chg + tr_qty_chg * um_rate.
            END.
            
            IF LAST-OF (tr_addr) THEN DO:
               vend_num = vend_num + 1.
               IF qty_chg > qty_max THEN DO:
                  qty_max = qty_chg.
                  vend_max = tr_addr.
               END.
            END.

               /*accept the receiver in 500 RW03 warehouse N-N-N*/
            IF CAN-DO ("M,S",tr_ship_type) OR tr_status = "N-N-N" THEN DO:
                qty_tot = qty_tot + tr_qty_loc * um_rate.
                cst_tot = cst_tot + tr_qty_loc * um_rate * tr_price * exc_rate.
            END.
            ELSE DO:
                qty_tot = qty_tot + tr_qty_chg * um_rate.
                cst_tot = cst_tot + tr_qty_chg * um_rate * tr_price * exc_rate.
            END.

            
            /*compute the average tax rate*/
            FOR EACH tx2d_det NO-LOCK WHERE tx2d_ref = tr_lot AND tx2d_nbr = tr_nbr AND tx2d_line = tr_line ,
                EACH prh_hist NO-LOCK WHERE tx2d_ref = prh_receiver AND tx2d_nbr = prh_nbr
                                        AND tx2d_line = prh_line AND prh_part = tr_part ,
                EACH exc1 WHERE exc_curr = tx2d_curr :

                IF tx2d_tax_in THEN 
                   tax_amt_in = tax_amt_in + tx2d_tax_amt * exc_rate.
                ELSE
                   tax_amt_out = tax_amt_out + tx2d_tax_amt * exc_rate.
            END.
/* DISP tr_part tr_addr vend_num tr_qty_loc um_rate qty_chg qty_tot cst_tot. */
     
        END.

        IF qty_tot <> 0 THEN
           ASSIGN cst_avg = cst_tot / qty_tot.
        ELSE 
           ASSIGN cst_avg = ?.

        IF cst_avg <> ?  THEN 
        DISP pt_part 
             pt_um
             pt_desc1                  FORMAT "X(30)"
             cst_avg                   FORMAT "->>,>>>,>>9.9<<<"  LABEL "AVG"
             vend_num                  FORMAT ">>9" LABEL "NUM"
             vend_max                  FORMAT "X(10)"  LABEL "VEND"
             (qty_max / qty_tot) * 100 FORMAT "->>9.9<<%" LABEL "Max%"
             qty_tot                   FORMAT "->>>,>>>,>>9.9<<<"  LABEL "QTY"
             cst_tot                   FORMAT "->>,>>>,>>>,>>9.9<<<"  LABEL "CST"
             (tax_amt_in + tax_amt_out) / (cst_tot - tax_amt_in) * 100 FORMAT "->>9.9<<%" LABEL "TAX%"
             tax_amt_in
             tax_amt_out 
        WITH DOWN WIDTH 180 STREAM-IO.
    END.
 /*OUTPUT CLOSE. */

END PROCEDURE.


PROCEDURE conv_um.
    DEFINE INPUT  PARAMETER part AS CHARACTER.
    DEFINE INPUT  PARAMETER um1 AS CHARACTER.   /*stock base unit */
    DEFINE INPUT  PARAMETER um2 AS CHARACTER.   /*conver unit*/
    DEFINE OUTPUT PARAMETER rate AS DECIMAL.

    FIND FIRST um_mstr NO-LOCK WHERE um_part = part AND um_um = um1 AND um_alt_um = um2 NO-ERROR .
    IF AVAILABLE um_mstr THEN
       rate = um_conv .
    ELSE DO:
       FIND FIRST um_mstr NO-LOCK WHERE um_um = um1 AND um_alt_um = um2 NO-ERROR .
       IF AVAILABLE um_mstr THEN
          rate = um_conv .
       ELSE 
          rate = 1 .
    END.
END PROCEDURE.
